package com.teamwings.common.jsqlparser;

import com.teamwings.common.jsqlparser.schema.SelectColumn;
import com.teamwings.common.jsqlparser.schema.SelectTable;
import com.teamwings.common.jsqlparser.schema.StructureQuery;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import org.springframework.util.DigestUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class AutoFormSelectVisitor implements SelectVisitor {

    /**
     * 字段列表
     */
    private final List<SelectColumn> selectColumnList = new ArrayList<>();
    /**
     * 表名列表
     */
    private final List<SelectTable> selectTableList = new ArrayList<>();
    /**
     * 查询Tree结构
     */
    private final StructureQuery structureQuery = new StructureQuery();
    /**
     * 是否有where
     */
    private Boolean hasWhere;
    /**
     * 是否有Limit
     */
    private Boolean hasLimit;
    /**
     * 是否有子查询
     */
    private Boolean hasSubSelect = false;
    /**
     * 是否有order_by
     */
    private Boolean hasOrderBy;


    public StructureQuery getStructureQuery() {
        return structureQuery;
    }

    public List<SelectColumn> getSelectColumnList() {
        return selectColumnList;
    }

    public List<SelectTable> getSelectTableList() {
        return selectTableList;
    }

    public Boolean getHasWhere() {
        return hasWhere;
    }

    public Boolean getHasLimit() {
        return hasLimit;
    }

    public Boolean getHasSubSelect() {
        return hasSubSelect;
    }


    @Override
    public void visit(PlainSelect pSelect) {
        // 处理查询字段
        List<SelectItem> selectItemList = pSelect.getSelectItems();
        this.handleSelectItemList(selectItemList);
        this.structureQuery.setSelectColumnList(this.selectColumnList);

        // 处理表名
        FromItem fromItem = pSelect.getFromItem();
        this.handleTableName(fromItem);
        this.structureQuery.setSelectTableList(this.selectTableList);
        // 处理hasWhere
        this.hasWhere = pSelect.getWhere() != null;
        this.hasLimit = pSelect.getLimit() != null;
        this.hasOrderBy = pSelect.getOrderByElements() != null && pSelect.getOrderByElements().size() > 0;
        // 处理表连接
        List<Join> joins = pSelect.getJoins();
        if (joins != null) {
            for (Join join : joins) {
                this.handleJoin(join);
            }
        }
    }

    // 处理join
    private void handleJoin(Join join) {
        this.handleTableName(join.getRightItem());
    }

    /**
     * 处理表名
     */
    private void handleTableName(FromItem fromItem) {
        String tableAliasName = null;
        if (fromItem.getAlias() != null) {
            Alias alias = fromItem.getAlias();
            tableAliasName = alias.getName();
        }

        if (fromItem instanceof Table) {
            // 处理一般表名
            Table table = (Table) fromItem;
            // getTableName() 目的是兼容表空间名，人大金仓有些sql会带有表空间名称
            String tableName = this.getTableName(table);
            this.addSelectTable(tableName, tableAliasName, false);
        } else if (fromItem instanceof SubSelect) {
            this.hasSubSelect = true;
            // 拿到字段数据，表名数据
            this.addSelectTable(null, tableAliasName, true);

            // 子查询
            AutoFormSelectVisitor subVisitor = new AutoFormSelectVisitor();
            ((SubSelect) fromItem).getSelectBody().accept(subVisitor);

            // 获取子查询的查询结构
            StructureQuery childStructureQuery = new StructureQuery();
            childStructureQuery.setSelectTableList(subVisitor.getSelectTableList());
            childStructureQuery.setSelectColumnList(subVisitor.getSelectColumnList());

            List<StructureQuery> subStructureQuery = this.structureQuery.getSubSelectList();
            if (subStructureQuery == null) {
                subStructureQuery = new ArrayList<>();
            }

            subStructureQuery.add(childStructureQuery);
            this.structureQuery.setSubSelectList(subStructureQuery);
        }
    }

    private void handleSelectItemList(List<SelectItem> selectItemList) {
        for (SelectItem selectItem : selectItemList) {
            // 用户自己在sql书写字段名
            if (selectItem instanceof SelectExpressionItem) {
                SelectExpressionItem expressionItem = (SelectExpressionItem) selectItem;
                String aliasName = ""; // 字段别名
                if (expressionItem.getAlias() != null) {
                    aliasName = expressionItem.getAlias().getName();
                }
                Expression expression = expressionItem.getExpression();
                analysisFunction(expression, aliasName);

            } else if (selectItem instanceof AllColumns) {
                // sql使用"*"查询
                String originalColumn = selectItem.toString();
                this.addSelectColumn(originalColumn, null, null);
            } else if (selectItem instanceof AllTableColumns) {
                // sql使用"xx.*"进行查询
                AllTableColumns allTableColumns = (AllTableColumns) selectItem;

                String originalColumn = allTableColumns.toString();
                this.addSelectColumn(originalColumn, null, null);
            } else {
                // 其他情况

            }
        }
    }

    @Override
    public void visit(SetOperationList setOperationList) {

    }

    @Override
    public void visit(WithItem withItem) {

    }

    @Override
    public void visit(ValuesStatement valuesStatement) {

    }

    /**
     * 这里目的是兼容表空间写法，旧oa项目人大金仓项目有些sql表名是带表空间的
     */
    private String getTableName(Table fromItem) {
        String name = fromItem.getName();  //表名
        String databaseName = fromItem.getDatabase().getDatabaseName();   //数据库名
        String schemaName = fromItem.getSchemaName();    //表空间名
        if (!Objects.isNull(databaseName) && !Objects.isNull(schemaName)) {
            return databaseName + "." + schemaName + "." + name;
        }
        if (Objects.isNull(databaseName) && !Objects.isNull(schemaName)) {
            return schemaName + "." + name;
        }
        return name;
    }

    /**
     * 查询语句查询字段解析
     */
    private Expression analysisFunction(Expression expression, String aliasName) {
        // 有些字段sql语句带“()”
        if (expression instanceof Parenthesis) {
            expression = ((Parenthesis) expression).getExpression();
        }

        if (judgeExpression(expression)) {
            // sql方法字段解析 case when, cast, concat

            // 解析cast
            if (expression instanceof CaseExpression) {
                CaseExpression caseExpression = (CaseExpression) expression;
//                Expression switchExpression = caseExpression.getSwitchExpression();

                String originalColumn = expression.toString();
                String tempName = "";
                try {
                    tempName = "case_" + DigestUtils.md5DigestAsHex(originalColumn.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }

                this.addSelectColumn(originalColumn, aliasName, tempName);
            } else if (expression instanceof Function) {    // 函数
                Function function = (Function) expression;
                List<Expression> expressions = function.getParameters().getExpressions();

                for (Expression ex : expressions) {
                    if (judgeExpression(ex)) {
                        expression = analysisFunction(ex, aliasName);
                    } else {
                        if (judgeValueExpression(ex)) {
                            expression = analysisFunction(ex, aliasName);
                        }
                    }
                }
                String originalColumn = expression.toString();
                String tempName = "";
                try {
                    tempName = "function_" + DigestUtils.md5DigestAsHex(originalColumn.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                this.addSelectColumn(originalColumn, aliasName, tempName);
            } else if (expression instanceof CastExpression) {    // cast函数
                Expression leftExpression = ((CastExpression) expression).getLeftExpression();
                if (judgeExpression(leftExpression)) {
                    expression = leftExpression;
                }
                String originalColumn = expression.toString();
                String tempName = "";
                try {
                    tempName = "cast_" + DigestUtils.md5DigestAsHex(originalColumn.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                this.addSelectColumn(originalColumn, aliasName, tempName);
            } else if (expression instanceof BinaryExpression) {   // concat函数
                Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();    //左侧表达式;

                String originalColumn = leftExpression.toString();
                String tempName = "";
                try {
                    tempName = "concat_" + DigestUtils.md5DigestAsHex(originalColumn.getBytes());
                } catch (Exception e) {
                    e.printStackTrace();
                }

                if (judgeExpression(leftExpression)) {
                    expression = leftExpression;
                } else {
                    if (judgeValueExpression(leftExpression)) {
                        this.addSelectColumn(originalColumn, aliasName, tempName);
                    }
                    expression = ((BinaryExpression) expression).getRightExpression();     // 右侧表达式
                }

            }
        } else if (judgeValueExpression(expression)) {
            // 一般字段解析
            String originalColumn = null;
            if (expression != null) {
                originalColumn = expression.toString();
            }

            this.addSelectColumn(originalColumn, aliasName, null);
        }
        return expression;
    }

    /**
     *
     * @param originalColumn 字段原始名称
     * @param aliasColumn    字段别名
     * @param tempColumn     字段临时名称 （主要用于查询字段用到了函数，但是没有设置别名的情况）
     */
    private void addSelectColumn(String originalColumn, String aliasColumn, String tempColumn) {
        SelectColumn selectColumn = new SelectColumn(originalColumn, aliasColumn, tempColumn);
        this.selectColumnList.add(selectColumn);
    }


    /**
     *
     * @param originalTableName 表名
     * @param tableAlias        表别名
     */
    private void addSelectTable(String originalTableName, String tableAlias, Boolean isSubSelect) {
        SelectTable selectTable = new SelectTable(originalTableName, tableAlias, isSubSelect);
        this.selectTableList.add(selectTable);
    }

    //判断查询字段中是否包含函数
    private boolean judgeExpression(Expression expression) {
        if (!Objects.isNull(expression)) {
            return expression instanceof Function || expression instanceof CastExpression
                    || expression instanceof CaseExpression || expression instanceof BinaryExpression;
        } else {
            return false;
        }
    }

    //判断expression是否为值类型
    private boolean judgeValueExpression(Expression expression) {
        return !(expression instanceof StringValue) && !(expression instanceof DoubleValue) &&
                !(expression instanceof LongValue);
    }

    public Boolean getHasOrderBy() {
        return hasOrderBy;
    }
}
